package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import javax.sql.rowset.FilteredRowSet;

import model.Patient;

import com.sun.rowset.FilteredRowSetImpl;

public class PatientSearch 
{
	
	private static List<Patient> patientFiles = null;

    public static List<Patient> getCertainColsPatients()
    {
        if (patientFiles == null)
        {
            patientFiles = new LinkedList<Patient>();
            Connection conn = null;
    		
    		try
    		{	
    			//String connStr = "jdbc:sqlserver://rl01s27mwd.database.windows.net:1433;user=cs3750@rl01s27mwd;password={9)zV{Ql>};database=weberdoc;";
    			String connStr = "jdbc:sqlserver://rl01s27mwd.database.windows.net:1433;database=patientsearch;user=cs3750@rl01s27mwd;password={9)zV{Ql>};";
    			DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
    			conn = DriverManager.getConnection(connStr);
    			
    			//Executing SQL Statement to get all of the values from the faculty table
    			Statement s = conn.createStatement(); //create statement using the connection object 
    			ResultSet rs = s.executeQuery("select FirstName AS firstName, MiddleInitial AS middleInitial, " +
    					"LastName AS lastName, Gender AS gender, BloodType AS bloodType, DateOfBirth AS dob, "
    					+ "Address AS address, City AS city, StateProvince AS state, PostalCode AS postalCode," +
    					"PatientID AS patientID from Patients"); //get results from the executed query statement
    			
    			//Retrieve data from faculty table
    			while(rs.next())
    			{
    				patientFiles.add(new Patient(rs.getString("firstName"), rs.getString("middleInitial"),
    						rs.getString("lastName"), rs.getString("gender"), rs.getString("bloodType"),  
    						rs.getString("dob"), rs.getString("address"), rs.getString("city"), rs.getString("state"),
    						rs.getString("postalCode"), rs.getString("patientID")));
    			}
    			//close the ResultSet and the Statement
    			rs.close();
    			s.close(); 
    			
    		}
    		catch(Exception e)
    		{
    			System.err.println(e.getMessage().toString());
    		}
    		finally
    		{
    			try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
    		}//end of the finally statement
            
        }
        return patientFiles;
    }
    
    
	public ResultSet search(HashMap<String,String> criteria, List<String> allergies, List<String> medications) {
		Connection conn = null;
		Statement st = null;
		FilteredRowSet frs = null;
		ResultSet rs = null;
		String sqlquery = null;

		try
		{
			sqlquery = 
/*View||run*/		//ordering scheme set up to allow easy modification of order items are created versus viewing them in the structure the statement is organized with.  while modifying order by view, return to run order at all other times
/* 0	0 */					"SELECT p.PatientID AS PatientID, p.FirstName AS FirstName, p.LastName AS LastName, p.MiddleInitial, p.Race, p.Gender, " + //add alias at by request
/* 1	1 */					"p.Insurer, p.DateOfBirth, p.BloodType, p.Religion, p.Country, p.StateProvince, p.City, p.PostalCode, p.Address, " + 
/* 2	2 */						//no columns displayed from PatientAllergies' lookup table
/* 3	3 */							"aTOpa.AllergyName AS 'AllergyName', " +
/* 5	4 */							"mTOpm.Name AS 'MedicationName', " +
/* 6	5 */								"dTOpm.FirstName AS 'PrescribingDoctorFirstName', dTOpm.LastName AS 'PrescribingDoctorLastName', dTOpm.MiddleInitial AS 'PrescribingDoctorMiddleInitial', dTOpm.Specialization AS 'PrescribingDoctorSpecialization', " +
/* 4	6 */						"pmTOp.Dosage, " +  //logically may be best after the medication?
/* 7	7 */						"'visits' AS 'Visits', vTOp.DateOfVisit, vTOp.ReasonForVisit, " +  //attempting to create a new column for use of adding all visits to it
/* 8	8 */							"lTOv.Name AS 'LocationName', " +
/* 9	9 */							"dTOv.FirstName AS 'DoctorFirstName', dTOv.LastName AS 'DoctorLastName', dTOv.MiddleInitial AS 'DoctorMiddleInitial', dTOv.Specialization AS 'DoctorSpecialization' " +

/* 0	0 */					"FROM Patients AS p, " +  //joining every table doesn't seem like this would work but i want to try
/* 1	1 */						"PatientAllergies AS paTOp, " +
/* 2	2 */							"Allergies AS aTOpa, " +
/* 3	3 */						"PatientMeds AS pmTOp, " +
/* 4	4 */							"Medications AS mTOpm, " +
/* 5	5 */							"Doctors AS dTOpm, " +
/* 6	6 */						"Visits AS vTOp, " +  
/* 7	7 */							"Locations AS lTOv, " +
/* 8	8 */							"Doctors AS dTOv " +

/* 0	0 */					"WHERE " +
/* 1	1 */						"p.PatientID = paTOp.PatientID AND " +
/* 2	2 */							"paTOp.AllergyID = aTOpa.AllergyID AND " +
/* 3	3 */						"p.PatientID = pmTOp.PatientID AND " +
/* 4	4 */							"pmTOp.MedicationID = mTOpm.MedicationID AND " +
/* 5	5 */							"pmTOp.DoctorID = dTOpm.DoctorID AND " +
/* 6	6 */						"p.PatientID = vTOp.PatientID AND " +
/* 7	7 */							"vTOp.LocationID = lTOv.LocationID AND " +
/* 8	8 */							"vTOp.DoctorID = dTOv.DoctorID ";					

			if (!allergies.isEmpty()) {
				sqlquery +=			"AND p.PatientID IN ( SELECT ponein.PatientID FROM PatientAllergies, aonein.Allergies " +
										"WHERE ponein.AllergyID = aonein.AllergyID ";
				for (int i = 0; i < allergies.size(); i++)
					sqlquery += "AND aonein.AllergyName LIKE '%" + allergies.get(i) + "%' ";
				sqlquery += " ) ";
			}
			
			conn = this.getSqlserverConnection();
			st = conn.createStatement();
			st.executeQuery(sqlquery);
			
			frs = new FilteredRowSetImpl();  // use of frs found at http://docs.oracle.com/javase/tutorial/jdbc/basics/filteredrowset.html
			frs.setCommand(sqlquery);
			frs.execute(conn);
			//and holy crap we now have a gigantic table -- good luck
		}
		catch (Exception e) {
			try {
				frs.close(); //may need to close other things??? thats what a GC is for!
			} catch (SQLException e1) {
				e1.printStackTrace();
			}  
		}
		
		return frs;
	}
	
	public ResultSet theOneMethodToDefineAllSearches(HashMap<String,String> criteria, List<String> allergies, List<String> medications) throws SQLException{
		//pre-processing in case no decision is made to include/exclude this
		if (!criteria.containsKey("MedicationCombination")) criteria.put("MedicationCombination", "AND");
		if (!criteria.containsKey("AlergyCombination")) criteria.put("AlergyCombination", "AND");
		
		String sqlquery =   "";
		if (!medications.isEmpty()) {
			sqlquery +=			"AND p.PatientID IN ( SELECT ptwoin.PatientID FROM ptwoin.PatientMeds, mtwoin.Medications " +
									"WHERE ptwoin.MedicationID = mtwoin.MedicationID ";
			for (int i = 0; i < medications.size(); i++)
				sqlquery += "AND ptwoin.Name LIKE '%" + medications.get(i) + "%' ";
			sqlquery += " ) "; //closes medications query whenever medications are searched on
		}
		String[] keys = criteria.keySet().toArray(new String[criteria.keySet().size()-1]);
		String value;
		for (String key : keys) {
			value = criteria.get(key);
			if (!value.matches("[ \t]*"))  // if the string is NOT empty/or only whitespace
				sqlquery += "AND " + key + " = " + value + " ";
									// this will work if all keys exactly mimic DataBase Column Names
									// look at the sql injection in action!
		}
		sqlquery += ";";

		Connection conn = null;
		Statement st = null;
		FilteredRowSet frs = null;
		ResultSet rs = null;

		try
		{
			sqlquery = "SELECT * FROM Patients";
			conn = this.getSqlserverConnection();
			st = conn.createStatement();
			st.executeQuery(sqlquery);
			
			frs = new FilteredRowSetImpl();  // use of frs found at http://docs.oracle.com/javase/tutorial/jdbc/basics/filteredrowset.html
//				frs.setCommand(sqlquery);
//				frs.setUsername("W01176037");
//				frs.setPassword("example");
//				frs.setUrl("jdbc:mysql://71.195.203.90:3308/W01176037");
			frs.execute();
			//and holy crap we now have a gigantic table -- good luck
		}
		catch (Exception e) {
			frs.close();  //may need to close other things??? thats what a GC is for!
		}
		
		frs.clearParameters();	//this will (hopefully) stop it from reconnecting to the database and updating in the event that I screwed up and have not understood the object's methods called below
		
		if (frs.first())  //if there is at least one row in the results
		{
			//prepare to do something amazing
			frs.setFetchDirection(ResultSet.FETCH_FORWARD);
			//create temp variables to do something amazing
			int CurrentPatientID;
			int currentPatientFirstRow = 1, currentRow = currentPatientFirstRow;  //keeping track of rows manually
			String tempAllergies, tempMedications, tempVisits;
			//hum diddly dumb do something amazing
			//set up for first patient
			CurrentPatientID = frs.getInt("PatientID");
			tempAllergies = tempMedications = tempVisits = "";
			do {
				if (CurrentPatientID != frs.getInt("PatientID")) //if moving to a patient we werent already working with?
				{
					frs.absolute(currentPatientFirstRow);
					frs.setString("AllergyName", tempAllergies);
					frs.setString("MedicationName", tempMedications);
					frs.setString("Visits", tempVisits);
					tempAllergies = tempMedications = tempVisits = "";
					frs.absolute(currentRow);
					CurrentPatientID = frs.getInt("PatientID");
					currentPatientFirstRow = currentRow;
				}
				if (tempAllergies.length() == 0) tempAllergies = frs.getString("AllergyName");
				else if (!tempAllergies.matches(".*" + frs.getString("AllergyName") + ".*") ) tempAllergies += ", " + frs.getString("AllergyName"); //adding wildly to this string, i'll fix it later
				if (tempMedications.length() == 0) frs.getString("MedicationName");
				else if (!tempMedications.matches(".*" + frs.getString("MedicationName") + ".*") ) tempMedications += ", " + frs.getString("MedicationName"); //adding wildly to this string, i'll fix it later
				tempVisits += frs.getDate("vTOp.DateOfVisit").getTime() + ", " +  //hooray time in milliseconds, everyone understands this!...
							  frs.getString("LocationName") + ", " +
							  "Dr. " + frs.getString("DoctorFirstName") + " "+ frs.getString("DoctorMiddleInitial") + ". " + 
							  frs.getString("DoctorLastName") + ", " + frs.getString("DoctorSpecialization") + ", " + 
							  frs.getString("vTOp.ReasonForVisit") + ";"; //adding wildly to this string, i'll fix it later
				if (currentPatientFirstRow != currentRow) { //only if this row is not the first row of this patient....
					frs.deleteRow();  //TODO determine if there needs to be some sort of commit each time this runs.
				}
				currentRow++;
			} while (frs.next());
		}
		return frs;
	}
	
	public ResultSet getAllPatients() {
		Connection conn = null;
		Statement st = null;
		FilteredRowSet frs = null;
		ResultSet rs = null;
		try {
			String sqlquery = "SELECT * FROM Patients";
			conn = this.getSqlserverConnection();
//				st = conn.createStatement();
//				st.executeQuery(sqlquery);
			
			frs = new FilteredRowSetImpl();  // use of frs found at http://docs.oracle.com/javase/tutorial/jdbc/basics/filteredrowset.html
			frs.setCommand(sqlquery);
			frs.execute(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return frs;
	}
	
	private Connection getSqlserverConnection() throws Exception {
		//String connStr = "jdbc:sqlserver://rl01s27mwd.database.windows.net:1433;user=cs3750@rl01s27mwd;password={9)zV{Ql>};database=weberdoc;";
		String connStr = "jdbc:sqlserver://rl01s27mwd.database.windows.net:1433;database=patientsearch;user=cs3750@rl01s27mwd;password={9)zV{Ql>};";
		DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
		Connection conn = DriverManager.getConnection(connStr);
		
		return conn;
	}
	
	public static void main(String[] args) throws SQLException {
		PatientSearch ps = new PatientSearch();
		ResultSet rs;
		rs = ps.search(new HashMap<String, String>(), new ArrayList<String>(), new ArrayList<String>());
		System.out.println("hi");
		while(rs.next())
		{
			System.out.println(	rs.getString(1)+"\t"+
								rs.getString(2)+"\t"+
								rs.getString(3)+"\t"+
								rs.getString(4)+"\t"+
								rs.getString(5)+"\t"+
								rs.getString(6)+"\t"+
								rs.getString(7)+"\t"+
								rs.getString(8)+"\t"+
								rs.getString(9)+"\t"+
								rs.getString(10)+"\t"+
								rs.getString(12)+"\t"+
								rs.getString(13)+"\t"+
								rs.getString(14)+"\t"+
								rs.getString(15)+"\t"+
								rs.getString(16)+"\t"+
								rs.getString(17)+"\t"+
								rs.getString(18)+"\t"+
								rs.getString(19)+"\t"+
								rs.getString(20)+"\t"+
								rs.getString(21)+"\t"+
								rs.getString(22)+"\t"+
								rs.getString(23)+"\t"+
								rs.getString(24)+"\t"+
								rs.getString(25)+"\t"+
								rs.getString(26)+"\t"+
								rs.getString(27)+"\t"+
								rs.getString(28)+"\t"+
								rs.getString(29)+"\t"+
								rs.getString(30)
								);
		}
	}
}
